﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class DAOPhieuNhap:DataProvider
    {
        public static DataTable getAll()
        {
            string select = "select maphieunhap,ngaynhap,nhacungcap.tenncc,phieunhap.mancc from phieunhap left join nhacungcap on nhacungcap.mancc = phieunhap.mancc";
            var da = new SqlDataAdapter(select, KetNoi());
            DataTable dt = new DataTable();
            da.Fill(dt);
            DongKetNoi();
            return dt;
        }

        public static int getMaPhieuNhap()
        {
            string select = "select max(maphieunhap)+1 from phieunhap";
            var cmd = new SqlCommand(select,KetNoi());
            int maphieunhap;
            try
            {
                 maphieunhap = (Int32)cmd.ExecuteScalar();
            }
            catch 
            {
                maphieunhap = 1;
            }
            
            DongKetNoi();
            return maphieunhap;
        }

        public static int add(DTOPhieuNhap obj)
        {
            string select = "select * from phieunhap";
            var da = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            var row = dt.NewRow();            
            row["NgayNhap"] = obj.NgayNhap;
            row["MaNCC"] = obj.MaNCC;            
            dt.Rows.Add(row);
            DongKetNoi();
            return da.Update(dt);
        }

        public static int edit(DTOPhieuNhap obj)
        {
            string select = "select * from phieunhap";
            var da = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);

            var row = dt.Rows.Find(obj.MaPhieuNhap);
            row.BeginEdit();
            row["NgayNhap"] = obj.NgayNhap;
            row["MaNCC"] = obj.MaNCC;
            row.EndEdit();

            DongKetNoi();
            return da.Update(dt);
        }

        public static int del(DTOPhieuNhap obj)
        {
            string select = "select * from phieunhap";
            var da = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            var ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source,"phieunhap");
            da.Fill(ds,"phieunhap");

            select = "select * from chitietphieunhap";
            var oda = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            oda.FillSchema(ds, SchemaType.Source, "chitietphieunhap");
            
            ds.Relations.Add(new DataRelation("phieunhap_chitiet", ds.Tables["phieunhap"].Columns["maphieunhap"], ds.Tables["chitietphieunhap"].Columns["maphieunhap"], true));

            var row = ds.Tables["phieunhap"].Rows.Find(obj.MaPhieuNhap);
            row.Delete();

            DongKetNoi();
            return da.Update(ds);
        }
    }
}
